using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Raptor.Client
{
  public partial class frmSearchColumns : Form
  {
    public frmSearchColumns()
    {
      InitializeComponent();
    }

    private frmMain m_ReturnForm;

    private List<List<String>> m_DOOptions = new List<List<string>>();

	  public frmMain returnForm
	  {
		  get { return m_ReturnForm;}
		  set { m_ReturnForm = value;}  
	  }
	
    private DataTable m_TableColumns;

    public DataTable TableColumns
    { 
      get { return m_TableColumns; }
      set { m_TableColumns = value; }
    }

    private List<List<String>> m_SelectedSearchResults;

    public List<List<String>> selectedSearchResults
    {
      get { return m_SelectedSearchResults; }
      set { m_SelectedSearchResults = value; }
    }

    private List<List<String>> m_SearchResults;

    public List<List<String>> SearchResults
    {
      get { return m_SearchResults; }
      set { m_SearchResults = value; }
    }
    private SqlConnection m_ActiveConn;

    public SqlConnection ActiveConn
    {
      get { return m_ActiveConn; }
      set { m_ActiveConn = value; }
    }
    
    private List<List<String>> m_SelectedList;

    public List<List<String>> SelectedList
    {
      get { return m_SelectedList; }
      set { m_SelectedList = value; }
    }

    private string m_TableName;

    public string TableName
    {
      get { return m_TableName; }
      set { m_TableName = value; }
    }

    private string m_StoredProcName;

    public string StoredProcName
    {
      get { return m_StoredProcName; }
      set { m_StoredProcName = value; }
    }

    private List<List<String>> m_SelectedDOOptions;
    /// <summary>
    /// Comments
    /// </summary>
    public List<List<String>> SelectedDOOptions
    {
      get { return m_SelectedDOOptions; }
      set { m_SelectedDOOptions = value; }
    }

    private void LoadGridFromStoredProcedure(string sStoredProcName)
    {
      int iRowIndex = 0;
      int iSelectedCount = 0, iSelectedResultCount = 0;
      string FieldLength = "";
      bool bIsNullable = false;
      bool bIsPrimaryKey = false;
      string sPrimaryKeyColumnName = "";
      DataSet DSStoredProcResult = new DataSet();

      //string sSQLString = "select 'ColumnName' = col.name " +
      //              "from sys.objects tbl " +
      //              "inner join sys.objects pk on pk.parent_object_id = tbl.object_id " +
      //              "inner join sys.index_columns idxcol on idxcol.object_id = tbl.object_id " +
      //              "inner join sys.columns col on col.object_id = tbl.object_id and col.column_id = idxcol.column_id " +
      //              "where tbl.type = 'u' " +
      //              " and pk.type = 'pk' " +
      //              " and tbl.name = '" + sTableName + "'";

      string sSQLString = sStoredProcName;

      SqlDataAdapter SQA = new SqlDataAdapter(sSQLString, ActiveConn);

      try
      {
        SQA.Fill(DSStoredProcResult);

        //if (DSStoredProcResult.Tables[0].Rows.Count > 0)
        //{
        //  foreach (DataRow dr in DSStoredProcResult.Tables[0].Rows)
        //  {
        //    sPrimaryKeyColumnName = dr["ColumnName"].ToString();
        //  }
        //}

        foreach (DataColumn datasetcolumn in DSStoredProcResult.Tables[0].Columns)
        {
          string sdatasetcolumnName = datasetcolumn.ColumnName;
          foreach (DataRow currentColumnRow in TableColumns.Rows)
          {
            string stablecolumnName = currentColumnRow["COLUMN_NAME"].ToString();
            if (currentColumnRow["COLUMN_NAME"].ToString() == datasetcolumn.ColumnName)
            {
              if (currentColumnRow["DATA_TYPE"].ToString().ToUpper() == "VARCHAR" || currentColumnRow["DATA_TYPE"].ToString().ToUpper() == "CHAR")
                FieldLength = currentColumnRow["CHARACTER_MAXIMUM_LENGTH"].ToString();
              else
                FieldLength = "";

              if (currentColumnRow["IS_NULLABLE"].ToString().ToUpper() == "YES")
                bIsNullable = true;
              else
                bIsNullable = false;

              if (currentColumnRow["COLUMN_NAME"].ToString() == sPrimaryKeyColumnName)
                bIsPrimaryKey = true;
              else
                bIsPrimaryKey = false;
              //long lOrdinalPos = long.Parse(currentColumnRow["ORDINAL_POSITION"].ToString()) - 1;
              iRowIndex = GrdSearchColumns.Rows.Add(false, currentColumnRow["COLUMN_NAME"], currentColumnRow["DATA_TYPE"], FieldLength, bIsNullable, bIsPrimaryKey, currentColumnRow["ORDINAL_POSITION"], false, false, false, false);

              break;
            }
          }
        }

        //foreach (DataRow currentColumnRow in TableColumns.Rows)
        //{
        //  string colunname = currentColumnRow["COLUMN_NAME"].ToString();
        //  //string sPrimary = currentColumnRow["KEY"].ToString();
        //  if (currentColumnRow["DATA_TYPE"].ToString().ToUpper() == "VARCHAR")
        //    FieldLength = currentColumnRow["CHARACTER_MAXIMUM_LENGTH"].ToString();
        //  else
        //    FieldLength = "";

        //  if (currentColumnRow["IS_NULLABLE"].ToString().ToUpper() == "YES")
        //    bIsNullable = true;
        //  else
        //    bIsNullable = false;

        //  if (currentColumnRow["COLUMN_NAME"].ToString() == sPrimaryKeyColumnName)
        //    bIsPrimaryKey = true;
        //  else
        //    bIsPrimaryKey = false;
        //  //long lOrdinalPos = long.Parse(currentColumnRow["ORDINAL_POSITION"].ToString()) - 1;
        //  iRowIndex = GrdSearchColumns.Rows.Add(false, currentColumnRow["COLUMN_NAME"], currentColumnRow["DATA_TYPE"], FieldLength, bIsNullable, bIsPrimaryKey, currentColumnRow["ORDINAL_POSITION"], false, false, false, false);
        //}

        GrdSearchColumns.Sort(OrdinalPosition, ListSortDirection.Ascending);

        if (SelectedList != null && SelectedList.Count > 0)
        {
          foreach (DataGridViewRow row in GrdSearchColumns.Rows)
          {
            for (int i = 0; i < SelectedList.Count; i++)
            {
              if (SelectedList[i][0] == row.Cells[1].Value.ToString())
              {
                if (bool.Parse(SelectedList[i][5]))
                {
                  (GrdSearchColumns.Rows[row.Index].Cells[0] as DataGridViewCheckBoxCell).Value = true;
                  iSelectedCount++;
                }
                if (bool.Parse(SelectedList[i][4]))
                  (GrdSearchColumns.Rows[row.Index].Cells[5] as DataGridViewCheckBoxCell).Value = true;

                if (bool.Parse(SelectedList[i][7]))
                  (GrdSearchColumns.Rows[row.Index].Cells[8] as DataGridViewCheckBoxCell).Value = true;

                if (bool.Parse(SelectedList[i][8]))
                  (GrdSearchColumns.Rows[row.Index].Cells[9] as DataGridViewCheckBoxCell).Value = true;

                if (bool.Parse(SelectedList[i][9]))
                  (GrdSearchColumns.Rows[row.Index].Cells[10] as DataGridViewCheckBoxCell).Value = true;

                break;
              }
            }
            if (selectedSearchResults != null && selectedSearchResults.Count > 0)
            {
              for (int i = 0; i < selectedSearchResults.Count; i++)
              {
                if (selectedSearchResults[i][0] == row.Cells[1].Value.ToString())
                {
                  (GrdSearchColumns.Rows[row.Index].Cells[7] as DataGridViewCheckBoxCell).Value = true;
                  iSelectedResultCount++;
                }
              }
            }
          }
        }
      }
      catch (SqlException ex)
      {
        MessageBox.Show(ex.Message);
      }
    }

    private void LoadGridFromTable(string sTableName)
    {
      int iRowIndex = 0;
      int iSelectedCount = 0, iSelectedResultCount = 0;
      string FieldLength = "";
      bool bIsNullable = false;
      bool bIsPrimaryKey = false;
      string sPrimaryKeyColumnName = "";
      DataSet DSPrimaryKey = new DataSet();

      string sSQLString = "select 'ColumnName' = col.name " +
                    "from sys.objects tbl " +
                    "inner join sys.objects pk on pk.parent_object_id = tbl.object_id " +
                    "inner join sys.index_columns idxcol on idxcol.object_id = tbl.object_id " +
                    "inner join sys.columns col on col.object_id = tbl.object_id and col.column_id = idxcol.column_id " +
                    "where tbl.type = 'u' " +
                    " and pk.type = 'pk' " +
                    " and tbl.name = '" + sTableName + "'";

      SqlDataAdapter SQA = new SqlDataAdapter(sSQLString, ActiveConn);

      try
      {
        SQA.Fill(DSPrimaryKey);

        if(DSPrimaryKey.Tables[0].Rows.Count > 0)
        {
          foreach(DataRow dr in DSPrimaryKey.Tables[0].Rows)
          {
            sPrimaryKeyColumnName = dr["ColumnName"].ToString();
          }
        }

        foreach (DataRow currentColumnRow in TableColumns.Rows)
        {
          //string sPrimary = currentColumnRow["KEY"].ToString();
          if (currentColumnRow["DATA_TYPE"].ToString().ToUpper() == "VARCHAR")
            FieldLength = currentColumnRow["CHARACTER_MAXIMUM_LENGTH"].ToString();
          else
            FieldLength = "";

          if (currentColumnRow["IS_NULLABLE"].ToString().ToUpper() == "YES")
            bIsNullable = true;
          else
            bIsNullable = false;

          if (currentColumnRow["COLUMN_NAME"].ToString() == sPrimaryKeyColumnName)
            bIsPrimaryKey = true;
          else
            bIsPrimaryKey = false;
          //long lOrdinalPos = long.Parse(currentColumnRow["ORDINAL_POSITION"].ToString()) - 1;
          iRowIndex = GrdSearchColumns.Rows.Add(false, currentColumnRow["COLUMN_NAME"], currentColumnRow["DATA_TYPE"], FieldLength, bIsNullable, bIsPrimaryKey, currentColumnRow["ORDINAL_POSITION"], false, false, false, false);
        }

        GrdSearchColumns.Sort(OrdinalPosition, ListSortDirection.Ascending);

        if (SelectedList != null && SelectedList.Count > 0)
        {
          foreach (DataGridViewRow row in GrdSearchColumns.Rows)
          {
            for (int i = 0; i < SelectedList.Count; i++)
            {
              if (SelectedList[i][0] == row.Cells[1].Value.ToString())
              {
                if (bool.Parse(SelectedList[i][5]))
                {
                  (GrdSearchColumns.Rows[row.Index].Cells[0] as DataGridViewCheckBoxCell).Value = true;
                  iSelectedCount++;
                }
                if (bool.Parse(SelectedList[i][4]))
                  (GrdSearchColumns.Rows[row.Index].Cells[5] as DataGridViewCheckBoxCell).Value = true;

                if (bool.Parse(SelectedList[i][7]))
                  (GrdSearchColumns.Rows[row.Index].Cells[8] as DataGridViewCheckBoxCell).Value = true;

                if (bool.Parse(SelectedList[i][8]))
                  (GrdSearchColumns.Rows[row.Index].Cells[9] as DataGridViewCheckBoxCell).Value = true;

                if (bool.Parse(SelectedList[i][9]))
                  (GrdSearchColumns.Rows[row.Index].Cells[10] as DataGridViewCheckBoxCell).Value = true;

                break;
              }
            }
            if (selectedSearchResults != null && selectedSearchResults.Count > 0)
            {
              for (int i = 0; i < selectedSearchResults.Count; i++)
              {
                if (selectedSearchResults[i][0] == row.Cells[1].Value.ToString())
                {
                  (GrdSearchColumns.Rows[row.Index].Cells[7] as DataGridViewCheckBoxCell).Value = true;
                  iSelectedResultCount++;
                }
              }
            }
          }
        }
      }
      catch (SqlException ex)
      {
        MessageBox.Show(ex.Message);
      }
    }

    public void LoadTableColumns()
    {
      //int iRowIndex = 0;
      int iSelectedCount = 0, iSelectedResultCount = 0;
      //string FieldLength = "";
      //bool bIsNullable = false;
      //bool bIsPrimaryKey = false;
      //string sPrimaryKeyColumnName = "";
      //DataSet DSPrimaryKey = new DataSet();
      if (this.StoredProcName != null)
        LoadGridFromStoredProcedure(this.StoredProcName);
      else
        LoadGridFromTable(this.m_TableName);

      //string sSQLString = "select 'ColumnName' = col.name " +
      //                    "from sys.objects tbl " +
      //                    "inner join sys.objects pk on pk.parent_object_id = tbl.object_id " +
      //                    "inner join sys.index_columns idxcol on idxcol.object_id = tbl.object_id " +
      //                    "inner join sys.columns col on col.object_id = tbl.object_id and col.column_id = idxcol.column_id " +
      //                    "where tbl.type = 'u' " +
      //                    " and pk.type = 'pk' " +
      //                    " and tbl.name = '" + this.m_TableName + "'";

      //SqlDataAdapter SQA = new SqlDataAdapter(sSQLString, ActiveConn);

      //try
      //{
      //  SQA.Fill(DSPrimaryKey);

      //  if(DSPrimaryKey.Tables[0].Rows.Count > 0)
      //  {
      //    foreach(DataRow dr in DSPrimaryKey.Tables[0].Rows)
      //    {
      //      sPrimaryKeyColumnName = dr["ColumnName"].ToString();
      //    }
      //  }

      //  foreach (DataRow currentColumnRow in TableColumns.Rows)
      //  {
      //    //string sPrimary = currentColumnRow["KEY"].ToString();
      //    if (currentColumnRow["DATA_TYPE"].ToString().ToUpper() == "VARCHAR")
      //      FieldLength = currentColumnRow["CHARACTER_MAXIMUM_LENGTH"].ToString();
      //    else
      //      FieldLength = "";

      //    if (currentColumnRow["IS_NULLABLE"].ToString().ToUpper() == "YES")
      //      bIsNullable = true;
      //    else
      //      bIsNullable = false;

      //    if (currentColumnRow["COLUMN_NAME"].ToString() == sPrimaryKeyColumnName)
      //      bIsPrimaryKey = true;
      //    else
      //      bIsPrimaryKey = false;
      //    //long lOrdinalPos = long.Parse(currentColumnRow["ORDINAL_POSITION"].ToString()) - 1;
      //    iRowIndex = GrdSearchColumns.Rows.Add(false, currentColumnRow["COLUMN_NAME"], currentColumnRow["DATA_TYPE"], FieldLength, bIsNullable, bIsPrimaryKey, currentColumnRow["ORDINAL_POSITION"], false, false, false, false);
      //  }

      //  GrdSearchColumns.Sort(OrdinalPosition, ListSortDirection.Ascending);

      //  if (SelectedList != null && SelectedList.Count > 0)
      //  {
      //    foreach (DataGridViewRow row in GrdSearchColumns.Rows)
      //    {
      //      for (int i = 0; i < SelectedList.Count; i++)
      //      {
      //        if (SelectedList[i][0] == row.Cells[1].Value.ToString())
      //        {
      //          if (bool.Parse(SelectedList[i][5]))
      //          {
      //            (GrdSearchColumns.Rows[row.Index].Cells[0] as DataGridViewCheckBoxCell).Value = true;
      //            iSelectedCount++;
      //          }
      //          if (bool.Parse(SelectedList[i][4]))
      //            (GrdSearchColumns.Rows[row.Index].Cells[5] as DataGridViewCheckBoxCell).Value = true;

      //          if (bool.Parse(SelectedList[i][7]))
      //            (GrdSearchColumns.Rows[row.Index].Cells[8] as DataGridViewCheckBoxCell).Value = true;

      //          if (bool.Parse(SelectedList[i][8]))
      //            (GrdSearchColumns.Rows[row.Index].Cells[9] as DataGridViewCheckBoxCell).Value = true;

      //          if (bool.Parse(SelectedList[i][9]))
      //            (GrdSearchColumns.Rows[row.Index].Cells[10] as DataGridViewCheckBoxCell).Value = true;

      //          break;
      //        }
      //      }
      //      if (selectedSearchResults != null && selectedSearchResults.Count > 0)
      //      {
      //        for (int i = 0; i < selectedSearchResults.Count; i++)
      //        {
      //          if (selectedSearchResults[i][0] == row.Cells[1].Value.ToString())
      //          {
      //            (GrdSearchColumns.Rows[row.Index].Cells[7] as DataGridViewCheckBoxCell).Value = true;
      //            iSelectedResultCount++;
      //          }
      //        }
      //      }
      //    }
      //  }

        //if (selectedSearchResults != null && selectedSearchResults.Count > 0)
        //{
        //  for (int i = 0; i < selectedSearchResults.Count; i++)
        //  {
        //    //if (selectedSearchResults[i][0] == currentColumnRow["COLUMN_NAME"].ToString())
        //    //{
        //    //  (GrdSearchColumns.Rows[iRowIndex].Cells[7] as DataGridViewCheckBoxCell).Value = true;
        //    //  iSelectedResultCount++;
        //    //}
        //  }
        //}

        if (SelectedDOOptions != null && SelectedDOOptions.Count > 0)
        {
          chkCreate.Checked = false;
          chkDelete.Checked = false;
          chkRetrieve.Checked = false;
          chkRetrieveAll.Checked = false;
          chkUpdate.Checked = false;
          chkAudit.Checked = false;
          chkDynamicQueries.Checked = false;
          chkLimit.Checked = false;
          chkSearchable.Checked = false;
          chkStandardLookup.Checked = false;
          chkStandardLookupDescription.Checked = false;

          for (int i = 0; i < SelectedDOOptions[0].Count; i++)
          {
            switch (SelectedDOOptions[0][i])
            {
              case "CREATE":
                {
                  chkCreate.Checked = true;
                  break;
                }
              case "DELETE":
                {
                  chkDelete.Checked = true;
                  break;
                }
              case "RETRIEVE":
                {
                  chkRetrieve.Checked = true;
                  break;
                }
              case "RETRIEVEALL":
                {
                  chkRetrieveAll.Checked = true;
                  break;
                }
              case "UPDATE":
                {
                  chkUpdate.Checked = true;
                  break;
                }
              case "AUDIT":
                {
                  chkAudit.Checked = true;
                  break;
                }
              case "DYNAMIC QUERIES":
                {
                  chkDynamicQueries.Checked = true;
                  break;
                }
              case "SEARCHABLE":
                {
                  chkSearchable.Checked = true;
                  break;
                }
              case "NOLIMIT":
                {
                  chkLimit.Checked = false;
                  txtSearchLimit.Text = "";
                  break;
                }
              case "STANDARD":
                {
                  chkStandardLookup.Checked = true;
                  break;
                }
              case "STANDARDDESCRIPTION":
                {
                  chkStandardLookupDescription.Checked = true;
                  break;
                }
              default:
                {
                  chkLimit.Checked = true;
                  txtSearchLimit.Text = SelectedDOOptions[0][i];
                  break;
                }
            }
          }
        }

        if (iSelectedCount == GrdSearchColumns.Rows.Count)
          chkSearchAll.Checked = true;
        else
          chkSearchAll.Checked = false;

        if (iSelectedResultCount == GrdSearchColumns.Rows.Count)
          chkResultAll.Checked = true;
        else
          chkResultAll.Checked = false;
      //}
      //catch(SqlException ex)
      //{
      //  MessageBox.Show(ex.Message);
      //}
    }

    private void frmSearchColumns_Load(object sender, EventArgs e)
    {
      if (this.TableColumns != null)
      {

        LoadTableColumns();
        if (GrdSearchColumns.Rows.Count > 0)
        {
          chkSearchAll.Enabled = true;
          btnSave.Enabled = true;
        }
        else
        {
          chkSearchAll.Enabled = false;
          btnSave.Enabled = false;
        }
      }
      else
      {
        if (this.StoredProcName != null)
          LoadTableColumns();
      }
    }

    private void btnCancel_Click(object sender, EventArgs e)
    {
      this.Close();
    }

    private void btnSave_Click(object sender, EventArgs e)
    {
      bool bPrimaryKeySpecified = false;
      List<List<String>> returnList = new List<List<string>>();
      List<String> DOList = new List<string>();
      List<List<String>> returnSearchList = new List<List<string>>();

      if (!chkCreate.Checked && !chkDelete.Checked && !chkRetrieve.Checked && !chkRetrieveAll.Checked && !chkUpdate.Checked)
      {
        MessageBox.Show("Please set your DO Options");
        return;
      }
      else
      {
        if(chkCreate.Checked)
          DOList.Add("CREATE");
        if(chkDelete.Checked)
          DOList.Add("DELETE");
        if(chkRetrieve.Checked)
          DOList.Add("RETRIEVE");
        if(chkRetrieveAll.Checked)
          DOList.Add("RETRIEVEALL");
        if(chkUpdate.Checked)
          DOList.Add("UPDATE");
        if (chkAudit.Checked)
          DOList.Add("AUDIT");
        if (chkDynamicQueries.Checked)
          DOList.Add("DYNAMIC QUERIES");
        if (chkSearchable.Checked)
          DOList.Add("SEARCHABLE");

        if (chkLimit.Checked)
          DOList.Add(txtSearchLimit.Text);
        else
          DOList.Add("NOLIMIT");
        if (chkStandardLookup.Checked)
          DOList.Add("STANDARD");
        if (chkStandardLookupDescription.Checked)
          DOList.Add("STANDARDDESCRIPTION");

        this.m_DOOptions.Add(DOList);
      }
      foreach (DataGridViewRow currentRow in GrdSearchColumns.Rows)
      {
        if(bool.Parse((currentRow.Cells[5] as DataGridViewCheckBoxCell).Value.ToString()))
        {
          bPrimaryKeySpecified = true;
          break;
        }
      }

      if(!bPrimaryKeySpecified)
      {
        MessageBox.Show("Please identify the primary key");
        return;
      }
      else
      {
        foreach (DataGridViewRow currentRow in GrdSearchColumns.Rows)
        {
          //if (bool.Parse((currentRow.Cells[0] as DataGridViewCheckBoxCell).Value.ToString()))
          {
            List<String> returnItems = new List<string>();
            returnItems.Add(currentRow.Cells[1].Value.ToString()); // Field Name
            returnItems.Add(currentRow.Cells[2].Value.ToString()); // Data Type
            returnItems.Add(currentRow.Cells[3].Value.ToString()); // Field Length
            returnItems.Add(currentRow.Cells[4].Value.ToString()); // Nullable
            returnItems.Add(currentRow.Cells[5].Value.ToString()); // Primary Key
            returnItems.Add(currentRow.Cells[0].Value.ToString()); // Search Param
            returnItems.Add(currentRow.Cells[6].Value.ToString()); // Ordinal Position
            returnItems.Add(currentRow.Cells[8].Value.ToString()); // Optional Search Retrieve Param
            returnItems.Add(currentRow.Cells[9].Value.ToString()); // Retrieve All Parameter
            returnItems.Add(currentRow.Cells[10].Value.ToString());// Dependant Parameter
            returnList.Add(returnItems);
          }
          if (bool.Parse((currentRow.Cells[7] as DataGridViewCheckBoxCell).Value.ToString()))
          {
            List<String> returnSearchItems = new List<string>();
            returnSearchItems.Add(currentRow.Cells[1].Value.ToString());
            returnSearchList.Add(returnSearchItems);
          }
        }
        returnForm.selectedTableColumns = returnList;
        returnForm.DOOptions = this.m_DOOptions;
        returnForm.selectedSearchResults = returnSearchList;
        
        this.Close();
      }
    }

    private void chkSearchAll_CheckedChanged(object sender, EventArgs e)
    {
      if (chkSearchAll.Checked)
      {
        foreach (DataGridViewRow currentRow in GrdSearchColumns.Rows)
        {
          //ListObjects
          (currentRow.Cells[0] as DataGridViewCheckBoxCell).Value = true;
        }
      }
      else
      {
        foreach (DataGridViewRow currentRow in GrdSearchColumns.Rows)
        {
          //ListObjects
          (currentRow.Cells[0] as DataGridViewCheckBoxCell).Value = false;
        }
      }
    }

    private void GrdSearchColumns_CurrentCellDirtyStateChanged(object sender, EventArgs e)
    {
      GrdSearchColumns.CommitEdit(DataGridViewDataErrorContexts.CurrentCellChange);
    }

    private void GrdSearchColumns_CellValueChanged(object sender, DataGridViewCellEventArgs e)
    {
      if (e.RowIndex >= 0)
      {
        switch (e.ColumnIndex)
        {
          case 0:
            {
              if (bool.Parse((GrdSearchColumns.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value.ToString()))
              {
                (GrdSearchColumns.Rows[e.RowIndex].Cells[9] as DataGridViewCheckBoxCell).Value = true;
              }
              //else
              //{
              //  (GrdSearchColumns.Rows[e.RowIndex].Cells[9] as DataGridViewCheckBoxCell).Value = false;
              //}
              break;
            }
          case 8:
            {
              if (bool.Parse((GrdSearchColumns.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value.ToString()))
              {
                if (GrdSearchColumns.Rows[e.RowIndex].Cells[2].Value.ToString() == "varchar")
                {
                  //String
                  (GrdSearchColumns.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value = false;
                  GrdSearchColumns.RefreshEdit();
                  MessageBox.Show("Sorry you cannot set Optional on a string/varchar field");
                  break;
                }
                if (e.RowIndex == 0)
                {
                  (GrdSearchColumns.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value = false;
                  GrdSearchColumns.RefreshEdit();
                  MessageBox.Show("Sorry you cannot set Optional on the primary key of the table");
                  break;
                }
                if (!bool.Parse((GrdSearchColumns.Rows[e.RowIndex].Cells[9] as DataGridViewCheckBoxCell).Value.ToString()))
                {
                  //Not Checked
                  (GrdSearchColumns.Rows[e.RowIndex].Cells[9] as DataGridViewCheckBoxCell).Value = true;
                }
              }
              break;
            }
          case 9:
            {
              if (!bool.Parse((GrdSearchColumns.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value.ToString()))
              {
                (GrdSearchColumns.Rows[e.RowIndex].Cells[0] as DataGridViewCheckBoxCell).Value = false;
                (GrdSearchColumns.Rows[e.RowIndex].Cells[8] as DataGridViewCheckBoxCell).Value = false;
              }
              break;
            }
          case 10:
            {
              if (bool.Parse((GrdSearchColumns.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value.ToString()))
              {
                if (GrdSearchColumns.Rows[e.RowIndex].Cells[2].Value.ToString() != "bigint" && GrdSearchColumns.Rows[e.RowIndex].Cells[2].Value.ToString() != "int")
                {
                  //String
                  (GrdSearchColumns.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value = false;
                  GrdSearchColumns.RefreshEdit();
                  MessageBox.Show("Sorry you cannot only set Dependent objects on foreign keys");
                  break;
                }
                if (e.RowIndex == 0)
                {
                  (GrdSearchColumns.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value = false;
                  GrdSearchColumns.RefreshEdit();
                  MessageBox.Show("Sorry you cannot set Dependent on the primary key of the table");
                  break;
                }
              }
              break;
            }
        }
      }
    }

    private void chkResultAll_CheckedChanged(object sender, EventArgs e)
    {
      if (chkResultAll.Checked)
      {
        foreach (DataGridViewRow currentRow in GrdSearchColumns.Rows)
        {
          //ListObjects
          (currentRow.Cells[7] as DataGridViewCheckBoxCell).Value = true;
        }
      }
      else
      {
        foreach (DataGridViewRow currentRow in GrdSearchColumns.Rows)
        {
          //ListObjects
          (currentRow.Cells[7] as DataGridViewCheckBoxCell).Value = false;
        }
      }

    }

    private void chkOptionalAll_CheckedChanged(object sender, EventArgs e)
    {
      if (chkOptionalAll.Checked)
      {
        foreach (DataGridViewRow currentRow in GrdSearchColumns.Rows)
        {
          //ListObjects
          (currentRow.Cells[8] as DataGridViewCheckBoxCell).Value = true;
        }
      }
      else
      {
        foreach (DataGridViewRow currentRow in GrdSearchColumns.Rows)
        {
          //ListObjects
          (currentRow.Cells[8] as DataGridViewCheckBoxCell).Value = false;
        }
      }
    }

    private void chkStandardLookup_CheckedChanged(object sender, EventArgs e)
    {
      if (chkStandardLookup.Checked)
      {
        foreach (DataGridViewRow row in GrdSearchColumns.Rows)
        {
          if (row.Cells[1].Value.ToString().IndexOf("_Active") > 0 || row.Cells[1].Value.ToString().IndexOf("_Default") > 0)
          {
            (row.Cells[8] as DataGridViewCheckBoxCell).Value = true;
          }
        }
      }
    }

    private void chkRetrieveAllParam_CheckedChanged(object sender, EventArgs e)
    {
      if (chkRetrieveAllParam.Checked)
      {
        foreach (DataGridViewRow currentRow in GrdSearchColumns.Rows)
        {
          //ListObjects
          (currentRow.Cells[9] as DataGridViewCheckBoxCell).Value = true;
        }
      }
      else
      {
        foreach (DataGridViewRow currentRow in GrdSearchColumns.Rows)
        {
          //ListObjects
          (currentRow.Cells[9] as DataGridViewCheckBoxCell).Value = false;
        }
      }
    }

    private void btnMerge_Click(object sender, EventArgs e)
    {
      //frmAddTables showTable = new frmAddTables();
      //showTable.ActiveConn = this.ActiveConn;
      //showTable.TableName = this.TableName;
      //showTable.ShowDialog();
    }
  }
}